﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Classes;
namespace FlightSystemDBLayer
{
    public class DBCity : ICity
    {
        


        public DBCity() // suspicious ? maybe not needed
        {

        }

        public IList<City> GetDBCities()
        {
            City a;
            DataSet ds = new DataSet();
            IList<City> list = new List<City>();
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = new SqlCommand(@"select * FROM City", SQLAccess.dbconn);
            da.Fill(ds);


            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                a = new City(dr["Name"].ToString(), dr["Country"].ToString());
                a.Id = Convert.ToInt32(dr["IDCity"]); 
                list.Add(a);
                
            }


            return list;
        }

        //delete if not used after finish
        //public void CreateCity(string name, string country, string airport)
        //{
        //    string sql = @"insert into City values (" + name + ", " + country + ", " + airport + ",)";
        //    SqlCommand cmd = SQLAccess.GetDbCommand(sql);
        //    cmd.ExecuteNonQuery();
        //    SQLAccess.Close();
        //}

        //public City FindCityByName(string name)
        //{
        //    string sql = @"select * from City where name = " + name;
        //    cmd = SQLAccess.GetDbCommand(sql);

        //    IDataReader dbReader;
        //    dbReader = cmd.ExecuteReader();

        //    City a;

        //    if (dbReader.Read())
        //    {
        //        a = new City(name, dbReader["airport"].ToString());
        //    }
        //    else
        //    {
        //        a = null;
        //        dbReader.Close();
        //        SQLAccess.Close();

        //    }
        //    return a;
        //}


        //public City FindCityByID(int IDCity)
        //{
        //    string sql = @"select * from City where IDCity = " + IDCity;
        //    cmd = SQLAccess.GetDbCommand(sql);
        //    IDataReader dbReader;
        //    dbReader = cmd.ExecuteReader();

        //    City a;
        //    if (dbReader.Read())
        //    {

        //        a = new City(dbReader["name"].ToString(), dbReader["Country"].ToString());
        //        SQLAccess.Close();//In order to make a nice code we will remove this line 

        //    }
        //    else
        //    {
        //        a = null;
        //        SQLAccess.Close();

        //    }
        //    return a;

        //}
    }
}